/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package rw.ac.kiosc.jdbc.school.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import rw.ac.kiosc.jdbc.school.dao.DepartmentDAO;
import rw.ac.kiosc.jdbc.school.orm.Department;
import rw.ac.kiosc.jdbc.school.orm.Student;

/**
 *
 * @author antoine
 */
public class DepartmentDAOImpl implements DepartmentDAO {

    private Connection connection;
    public static DepartmentDAOImpl instance;

    public static DepartmentDAOImpl getInstance(Connection connection) {
        if (instance == null) {
            instance = new DepartmentDAOImpl(connection);
        }
        return instance;
    }

    private DepartmentDAOImpl() {
    }

    private DepartmentDAOImpl(Connection connection) {
        this.connection = connection;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public synchronized Department saveDepartment(Department department) {

        Department ret = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statementOne.executeUpdate("INSERT INTO department(acronym, name, number_of_years) values(\'" + department.getAcronym() + "\', \'" + department.getName() + "\', " + department.getNumberOfYears() + ")");
            ResultSet departmentsList = statementOne.executeQuery("SELECT * FROM department WHERE acronym=\'" + department.getAcronym() + "\'");
            if (departmentsList.next()) {
                ret = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                ResultSet studentsList = statementTwo.executeQuery("SELECT * FROM student WHERE department_id=" + ret.getDepartmentId());
                while (studentsList.next()) {
                    ret.getStudents().add(new Student(studentsList.getInt("student_id"), studentsList.getString("registration_number"), studentsList.getString("name"), studentsList.getInt("year_of_study"), ret));
                }
            }
            return ret;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return ret;
        }
    }

    public synchronized Department updateDepartment(Department department) {
        Department ret = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            statementOne.executeUpdate("UPDATE department SET acronym=\'" + department.getAcronym() + "\', name=\'" + department.getName() + "\', number_of_years=" + department.getNumberOfYears() + "where department_id=" + department.getDepartmentId() + "");
            ResultSet departmentsList = statementOne.executeQuery("SELECT * FROM department WHERE department_id=" + department.getDepartmentId());
            if (departmentsList.next()) {
                ret = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                ResultSet studentsList = statementTwo.executeQuery("SELECT * FROM student WHERE department_id=" + ret.getDepartmentId());
                while (studentsList.next()) {
                    ret.getStudents().add(new Student(studentsList.getInt("student_id"), studentsList.getString("registration_number"), studentsList.getString("name"), studentsList.getInt("year_of_study"), ret));
                }
            }
            return ret;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return ret;
        }
    }

    public synchronized Department saveOrUpdateDepartment(Department department) {
        if (department.getDepartmentId() > 0) {
            try {
                Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ResultSet departmentsList = statement.executeQuery("SELECT acronym FROM department WHERE department_id=" + department.getDepartmentId());
                if (departmentsList.next()) {
                    return updateDepartment(department);
                } else {
                    return saveDepartment(department);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
                return null;
            }
        } else {
            return saveDepartment(department);
        }
    }

    public synchronized boolean deleteDepartment(Department department) {
        try {
            Statement statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            int n = statement.executeUpdate("DELETE FROM department WHERE department_id=" + department.getDepartmentId());
            return (n == 1) ? true : false;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }

    }

    public synchronized Department getDepartmentById(int departmentId) {
        Department ret = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet departmentsList = statementOne.executeQuery("SELECT * FROM department WHERE department_id=" + departmentId);
            if (departmentsList.next()) {
                ret = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                ResultSet studentsList = statementTwo.executeQuery("SELECT * FROM student WHERE department_id=" + ret.getDepartmentId());
                while (studentsList.next()) {
                    ret.getStudents().add(new Student(studentsList.getInt("student_id"), studentsList.getString("registration_number"), studentsList.getString("name"), studentsList.getInt("year_of_study"), ret));
                }
            }
            return ret;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return ret;
        }
    }

    public synchronized Department getDepartmentByAcronym(String acronym) {
        Department ret = null;
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet departmentsList = statementOne.executeQuery("SELECT * FROM department WHERE acronym=" + acronym);
            if (departmentsList.next()) {
                ret = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                ResultSet studentsList = statementTwo.executeQuery("SELECT * FROM student WHERE department_id=" + ret.getDepartmentId());
                while (studentsList.next()) {
                    ret.getStudents().add(new Student(studentsList.getInt("student_id"), studentsList.getString("registration_number"), studentsList.getString("name"), studentsList.getInt("year_of_study"), ret));
                }
            }
            return ret;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return ret;
        }
    }

    public synchronized List<Department> getDepartmentsByName(String departmentName) {
        List<Department> departments = new ArrayList<Department>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet departmentsList = statementOne.executeQuery("SELECT * FROM department WHERE name LIKE \'" + departmentName + "\'");
            while (departmentsList.next()) {
                Department ret = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                ResultSet studentsList = statementTwo.executeQuery("SELECT * FROM student WHERE department_id=" + ret.getDepartmentId());
                while (studentsList.next()) {
                    ret.getStudents().add(new Student(studentsList.getInt("student_id"), studentsList.getString("registration_number"), studentsList.getString("name"), studentsList.getInt("year_of_study"), ret));
                }
                departments.add(ret);
            }
            return departments;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return departments;
        }
    }

    public synchronized List<Department> getDepartmentsByNumberOfYears(int numberOfYears) {
        List<Department> departments = new ArrayList<Department>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet departmentsList = statementOne.executeQuery("SELECT * FROM department WHERE number_of_years=" + numberOfYears);
            while (departmentsList.next()) {
                Department ret = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                ResultSet studentsList = statementTwo.executeQuery("SELECT * FROM student WHERE department_id=" + ret.getDepartmentId());
                while (studentsList.next()) {
                    ret.getStudents().add(new Student(studentsList.getInt("student_id"), studentsList.getString("registration_number"), studentsList.getString("name"), studentsList.getInt("year_of_study"), ret));
                }
                departments.add(ret);
            }
            return departments;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return departments;
        }
    }

    public synchronized Department getDepartmentByStudent(Student student) {
        return getDepartmentById(student.getDepartment().getDepartmentId());
    }

    public List<Department> getAllDepartments() {
        List<Department> departments = new ArrayList<Department>();
        try {
            Statement statementOne = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            Statement statementTwo = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet departmentsList = statementOne.executeQuery("SELECT * FROM department");
            while (departmentsList.next()) {
                Department ret = new Department(departmentsList.getInt("department_id"), departmentsList.getString("acronym"), departmentsList.getInt("number_of_years"), departmentsList.getString("name"), new HashSet<Student>());
                ResultSet studentsList = statementTwo.executeQuery("SELECT * FROM student WHERE department_id=" + ret.getDepartmentId());
                while (studentsList.next()) {
                    ret.getStudents().add(new Student(studentsList.getInt("student_id"), studentsList.getString("registration_no"), studentsList.getString("name"), studentsList.getInt("year_of_study"), ret));
                }
                departments.add(ret);
            }
            return departments;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return departments;
        }
    }
}
